using UnityEngine;
using UnityEditor;
using System;
using System.Collections;
using System.Data; 
using System.Data.Odbc;
using System.Data.OleDb;
using System.IO;
using System.Text;

public class lwKitLoc : EditorWindow
{
	private static lwKitLoc s_window = null;
	private static bool s_bInitDone = false;
	
	private string m_sTextBasePath = string.Empty;
	private string m_sTextBaseSheetName = string.Empty;
	private string m_sKitLocPath = string.Empty;
	private string m_sKitLocSheetName = string.Empty;
	private string m_sFinalCsvPath = string.Empty;
	
	private bool m_bEncodeInUnicode = false;
	
	private int m_nColumnCount = 6;
	private string[] m_sColumnNameArray = null;
	
	[MenuItem("Tools/LWS/KitLoc")]
	static public void Init()
	{
		s_bInitDone = false;
		s_window = EditorWindow.GetWindow( typeof(lwKitLoc) ) as lwKitLoc;
		if( s_window != null )
		{
			s_window.InitAll();
		}
		s_bInitDone = true;
	}
	
	void InitAll()
	{
		m_sTextBasePath = PlayerPrefs.GetString( "m_sTextBasePath" );
		m_sTextBaseSheetName = PlayerPrefs.GetString( "m_sTextBaseSheetName" );
		m_sKitLocPath = PlayerPrefs.GetString( "m_sKitLocPath" );
		m_sKitLocSheetName = PlayerPrefs.GetString( "m_sKitLocSheetName" );
		m_sFinalCsvPath = PlayerPrefs.GetString( "m_sFinalCsvPath" );
		
		s_bInitDone = true;
	}
	
	void OnGUI()
	{
		if( s_bInitDone )
		{	
			GUILayout.BeginArea( new Rect( 10, 10, 500, 250 ), "" );
			
			// Text Base
			GUILayout.Label( "Text Base Path" );
			
			GUILayout.BeginHorizontal();
			
			m_sTextBasePath = GUILayout.TextField( m_sTextBasePath, GUILayout.Width(300) );
			
			if( GUILayout.Button( "Select", GUILayout.Width(50) ) )
				m_sTextBasePath = EditorUtility.OpenFilePanel( "Text Base Path", "", "xls" );
			
			m_sTextBaseSheetName = GUILayout.TextField( m_sTextBaseSheetName, GUILayout.Width(100) );
			
			GUILayout.EndHorizontal();
			
			// Kit Loc
			GUILayout.Label( "Kit Loc Path" );
			
			GUILayout.BeginHorizontal();
			
			m_sKitLocPath = GUILayout.TextField( m_sKitLocPath, GUILayout.Width(300) );
			
			if( GUILayout.Button( "Select", GUILayout.Width(50) ) )
				m_sKitLocPath = EditorUtility.OpenFilePanel( "Kit Loc Path", "", "xls" );
			
			m_sKitLocSheetName = GUILayout.TextField( m_sKitLocSheetName, GUILayout.Width(100) );
			
			GUILayout.EndHorizontal();
			
			// Final CSV Path
			GUILayout.Label( "Final CSV Path" );
			
			GUILayout.BeginHorizontal();
			
			m_sFinalCsvPath = GUILayout.TextField( m_sFinalCsvPath, GUILayout.Width(300) );
			
			if( GUILayout.Button( "Select", GUILayout.Width(50) ) )
				m_sFinalCsvPath = EditorUtility.SaveFilePanel( "Final CSV Path", "", "Text", "csv" );
			
			m_bEncodeInUnicode = GUILayout.Toggle( m_bEncodeInUnicode, "Encoding in Unicode" );
			
			GUILayout.EndHorizontal();
				
			if( GUILayout.Button( "Export" ) && !string.IsNullOrEmpty( m_sKitLocPath ) )
			{
				PlayerPrefs.SetString( "m_sTextBasePath", m_sTextBasePath );
				PlayerPrefs.SetString( "m_sTextBaseSheetName", m_sTextBaseSheetName );
				PlayerPrefs.SetString( "m_sKitLocPath", m_sKitLocPath );
				PlayerPrefs.SetString( "m_sKitLocSheetName", m_sKitLocSheetName );
				PlayerPrefs.SetString( "m_sFinalCsvPath", m_sFinalCsvPath );
				
				string sTextBaseText = ReadXLS( m_sTextBasePath, m_sTextBaseSheetName, true );
				string sKitLocText = ReadXLS( m_sKitLocPath, m_sKitLocSheetName, false );
				
				string sAllText = string.Concat( sTextBaseText, sKitLocText );
				
				File.WriteAllText( m_sFinalCsvPath, sAllText, m_bEncodeInUnicode ? Encoding.Unicode : Encoding.UTF8 );
				Debug.Log( "Export Completed ! CSV generated at " + m_sFinalCsvPath );
			}
			
			GUILayout.EndArea();
		}
	}
	
	private string ReadXLS( string sFileToRead, string sSheetName, bool bExportColumnName )
	{
		string sReturnedText = string.Empty;
		
//		string[] sSheetNameArray = GetExcelSheetNames( filetoread );
//		if( sSheetNameArray == null || sSheetNameArray.Length < 1 )
//			return string.Empty;
		
//		Debug.Log( "firstSheetName " + sSheetNameArray[0] );
		
		// Must be saved as excel 2003 workbook, not 2007, mono issue really
		string con = "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq="+sFileToRead+";";
		
//		Debug.Log(con);
		
		string yourQuery = string.Empty;
		
		// our odbc connector 
		OdbcConnection oCon = new OdbcConnection( con );
		
		// open the connection 
		oCon.Open();
		
		/*
		yourQuery = "SELECT * FROM Table_Name";
		
		OdbcDataAdapter rTableAdapt = new OdbcDataAdapter( yourQuery, oCon );
		
		DataTable dtTableData = new DataTable("TableData");
		
		try
		{
			// Fill data
			rTableAdapt.Fill( dtTableData );
		}
		finally
		{
			// close that reader! 
			rTableAdapt.Dispose();
		}
		
		Debug.Log( "First Table " + dtTableData.Rows[0][dtTableData.Columns[0].ColumnName].ToString() );
		
		
		try
		{
			DataTable tableschema = oCon.GetSchema( "TABLES" );
		
			if( tableschema != null )
			{		
				Debug.Log( "First Table " + tableschema.Rows[0]["TABLE_NAME"].ToString() );
			}
			else
				Debug.LogError( "Error : no tableschema!" );
		}
		finally
		{
		}
		
		return string.Empty;
		*/
		
//		string sSheetName = sSheetNameArray[0];
		
		yourQuery = "SELECT * FROM [" + sSheetName + "$]"; 
		
		// our command object
		OdbcDataAdapter rDataAdapt = new OdbcDataAdapter( yourQuery, oCon );

		// table to hold the data 
		DataTable dtYourData = new DataTable("YourData");

		// Try read
		try
		{
			// Fill data
			rDataAdapt.Fill( dtYourData );
		}
		finally
		{
			// close that reader! 
			rDataAdapt.Dispose();
		}
		// wow look at us go now! we are on a roll!!!!! 
		// lets now see if our table has the spreadsheet data in it, shall we? 
		
		// close your connection to the spreadsheet! 
		oCon.Close(); 
		
		string sData = string.Empty;
		
		int nRowCount = dtYourData.Rows.Count;
//		Debug.Log( "nRowCount " + nRowCount );
		m_nColumnCount = dtYourData.Columns.Count;
		
		if( nRowCount > 0 && m_nColumnCount > 0 ) 
		{ 
			if( bExportColumnName )
			{
				bool bCreateColumnNameArray = (m_sColumnNameArray == null);
				
				if( bCreateColumnNameArray )
					m_sColumnNameArray = new string[m_nColumnCount];
				
				for( int nCol = 0; nCol < m_nColumnCount; ++nCol )
				{
					sData = ConvertLangName( dtYourData.Columns[nCol].ColumnName );
					
					if( bCreateColumnNameArray )
						m_sColumnNameArray[nCol] = sData;
					
					sReturnedText = string.Concat( sReturnedText, sData );
					if( nCol < m_nColumnCount - 1 )
						sReturnedText = string.Concat( sReturnedText, ";" );
				}
				
				sReturnedText = string.Concat( sReturnedText, "\n" );
			}
			
			if( m_sColumnNameArray == null || m_sColumnNameArray.Length == 0 )
			{
				Debug.LogError( "No Column Name Array created !!!" );
				return string.Empty;
			}
			
			// Test column count
			if( dtYourData.Columns.Count != m_nColumnCount )
			{
				Debug.LogError( "No Same Column Count !!!" );
				return string.Empty;
			}
			
			// Reindex columns
			int[] nColumnIndexArray = new int[m_nColumnCount];
			for( int i = 0; i < m_nColumnCount; ++i )
			{
				for( int nCol = 0; nCol < m_nColumnCount; ++nCol )
				{
					sData = ConvertLangName( dtYourData.Columns[nCol].ColumnName );
					
					if( sData == m_sColumnNameArray[i] )
					{
						nColumnIndexArray[i] = nCol;
						break;
					}
				}
			}
			
			bool bSkipRow = false;
			
			// do something with the data here 
			// but how do I do this you ask??? good question! 
			for( int i = 0; i < nRowCount; i++ )
			{ 
				// for giggles, lets see the column name then the data for that column! 
//                Debug.Log(dtYourData.Columns[0].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[0].ColumnName].ToString() + 
//                    "  |  " + dtYourData.Columns[1].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[1].ColumnName].ToString() + 
//                    "  |  " + dtYourData.Columns[2].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[2].ColumnName].ToString());
				
				bSkipRow = false;
				
				for( int nCol = 0; nCol < m_nColumnCount; ++nCol )
				{
					sData = dtYourData.Rows[i][dtYourData.Columns[nColumnIndexArray[nCol]].ColumnName].ToString();
					
					// If nothing, find existing data in row
					if( string.IsNullOrEmpty( sData ) )
					{
						// If no Id, continue
						if( nCol == 0 )
						{
							bSkipRow = true;
							break;
						}
						
						for( int j = 1; j < m_nColumnCount; ++j )
						{
							sData = dtYourData.Rows[i][dtYourData.Columns[j].ColumnName].ToString();
							if( !string.IsNullOrEmpty( sData ) )
								break;
						}
					}
					
					sData = VerifyTextData( sData );
					
					sReturnedText = string.Concat( sReturnedText, sData );
					if( nCol < m_nColumnCount - 1 )
						sReturnedText = string.Concat( sReturnedText, ";" );
				}
				
				sReturnedText = string.Concat( sReturnedText, "\n" );
				
				if( bSkipRow )
					continue;
			}
			
			sReturnedText = string.Concat( sReturnedText, "\n" );
		}
		
		dtYourData.Reset();
		dtYourData = null;
		
		return sReturnedText;
	}
	
	/// <summary>
	/// This mehtod retrieves the excel sheet names from 
	/// an excel workbook.
	/// </summary>
	/// <param name="excelFile">The excel file.</param>
	/// <returns>String[]</returns>
	private string[] GetExcelSheetNames(string excelFile)
	{
	  OleDbConnection objConn = null;
	  System.Data.DataTable dt = null;
	
	  try
	  {
	    // Connection String. Change the excel file to the file you
	    // will search.
	    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
	        "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
	    // Create connection object by using the preceding connection string.
	    objConn = new OleDbConnection(connString);
	    // Open connection with the database.
	    objConn.Open();
	    // Get the data table containg the schema guid.
	    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
	 
	    if(dt == null)
	    {
	      return null;
	    }
	
	    string[] excelSheets = new string[dt.Rows.Count];
	    int i = 0;
	
	    // Add the sheet name to the string array.
	    foreach(DataRow row in dt.Rows)
	    {
	      excelSheets[i] = row["TABLE_NAME"].ToString();
	      i++;
	    }
	
	    // Loop through all of the sheets if you want too...
	    for(int j=0; j < excelSheets.Length; j++)
	    {
	      // Query each excel sheet.
	    }
	
	    return excelSheets;
	  }
	  catch(Exception ex)
	  {
		Debug.Log( "Exception " + ex );
	    return null;
	  }
	  finally
	  {
	    // Clean up.
	    if(objConn != null)
	    {
	      objConn.Close();
	      objConn.Dispose();
	    }
	    if(dt != null)
	    {
	      dt.Dispose();
	    }
	  }
	}
	
	private string ConvertLangName( string sLang )
	{
		if( string.IsNullOrEmpty( sLang ) )
			return string.Empty;
		if( sLang == "US" )
			sLang = "EN";
		return sLang;
	}
	
	private string VerifyTextData( string sText )
	{
		if( string.IsNullOrEmpty( sText ) )
			return string.Empty;
		sText = ReplaceSpecialChar( sText, "’", "'" );
		sText = ReplaceSpecialChar( sText, "«", "'" );
		sText = ReplaceSpecialChar( sText, "»", "'" );
		sText = ReplaceSpecialChar( sText, "…", "..." );
//		sText = ReplaceSpecialChar( sText, " ", " " ); // Espace insécable
		sText = ReplaceSpecialChar( sText, "—", ":" );
		sText = ReplaceSpecialChar( sText, ";", "," );
		return sText;
	}
	
	private string ReplaceSpecialChar( string sText, string sSpecial, string sReplacement )
	{
		if( sText.Contains( sSpecial ) )
		{
			sText = sText.Replace( sSpecial, sReplacement );
			Debug.LogWarning( "Warning ! " + sText + " contains " + sSpecial + " replaced by " + sReplacement );
		}
		return sText;
	}
}
